package com.bdqn.tour.service;

import com.bdqn.tour.pojo.Ticket;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.bdqn.tour.servlet.Tj.SearchTicketsServlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TicketService {
    private static final Logger logger = LoggerFactory.getLogger(SearchTicketsServlet.class);
    private Connection connection;

    public TicketService(Connection connection) throws SQLException {
        this.connection = connection;
        if (this.connection == null || this.connection.isClosed()) {
            logger.error("Database connection is not valid");
        } else {
            logger.info("Database connection established successfully");
        }
    }

    public TicketService() {

    }

    public List<Ticket> getTickets(String type, String tripType, String departureCity, String arrivalCity, String departureDate, String returnDate, boolean hasInfant, boolean hasChild) throws SQLException {
        System.out.println("getTickets method called");
        logger.info("Search Tickets Parameters: type={}, tripType={}, departureCity={}, arrivalCity={}, departureDate={}, returnDate={}, hasInfant={}, hasChild={}",
                type, tripType, departureCity, arrivalCity, departureDate, returnDate, hasInfant, hasChild);

        List<Ticket> tickets = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM tickets WHERE departure_city = ? AND arrival_city = ? AND departure_date = ? AND ticketType = ?");

        List<Object> params = new ArrayList<>();
        params.add(departureCity);
        params.add(arrivalCity);
        params.add(java.sql.Date.valueOf(departureDate));
        params.add(tripType);

        if ("return".equalsIgnoreCase(type) && returnDate != null && !returnDate.isEmpty()) {
            sql.append(" AND return_date = ?");
            params.add(java.sql.Date.valueOf(returnDate));
        }

        int paramIndex = 5;
        if (hasInfant) {
            sql.append(" AND has_infant_option = true");
        }

        if (hasChild) {
            sql.append(" AND has_child_option = true");
        }

        logger.info("Executing SQL query: {}", sql.toString());
        logger.info("Query parameters: {}", params);

        try (PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
                logger.debug("Setting parameter {}: {}", i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Ticket ticket = new Ticket();
                ticket.setId(rs.getString("id"));
                ticket.setTicketType(rs.getString("ticketType"));
                ticket.setDepartureCity(rs.getString("departure_city"));
                ticket.setArrivalCity(rs.getString("arrival_city"));
                ticket.setDepartureDate(rs.getDate("departure_date").toLocalDate());
                ticket.setStartTime(rs.getTime("start_time").toLocalTime());
                ticket.setEndTime(rs.getTime("end_time").toLocalTime());
                ticket.setPrice(rs.getDouble("price"));
                tickets.add(ticket);
                logger.debug("Found ticket: {}",ticket);
            }
            logger.info("Query returned {} results", tickets.size()); // 添加日志输出
        } catch (SQLException e) {
            logger.error("Error executing SQL query: {}", e.getMessage(), e);
            throw e;
        }
        return tickets;
    }
}
